package com.cjbdi.service;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import org.hibernate.SQLQuery;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.core.env.Environment;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import com.cjbdi.util.NumUtil;

/**
 * SfglCsxService
 */
@Service
@Transactional
public class SfglCsxService{
	@Autowired
    Environment env;
	@Autowired  
    @PersistenceContext  
    private EntityManager entityManager;// 注入EntityManager实例 
	@Autowired
	BaseService baseService;
	//String csxdate ="";
	//和去年同比 
	public List getGsList(String kind,String date, String fybm){  
    	Query query = entityManager.createNativeQuery("select date,jbfy,fymc,cx0006,cx0612,cx1224,cx2436,cx3600,num,result from sfzs_result_csx where date in('"+date+"','"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"') and kind='"+kind+"' and jbfy='"+fybm+"' order by date desc");  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //全国排名表格
    /*public List getQgTableList(String date, boolean isQuanguo){  
    	StringBuffer sql = new StringBuffer("select jbfy,fy,lastyearzs,yearzs,cz,rank() over(order by yearzs) as rank from(select jbfy,fymc as fy," + 
    			"sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' then result else 0 end )as lastyearzs," + 
    			"sum(case when date='"+date+"' then result else 0 end )as yearzs,"+ 
    			"(sum(case when date='"+date+"' then result else 0 end) - sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' then result else 0 end )) as cz  from sfzs_result_csx " + 
    			"where (jbfy not in('X00','000') or jbfy is null) and date in('"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"','"+date+"') and kind ='高'");
    	if(isQuanguo) {
    		sql.append(" and fymc='全国'");
    	}else {
    		sql.append(" and fymc!='全国'");
    	}
    	sql.append(" GROUP BY jbfy,fymc)t;");
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }*/
    //中院排名表格
    /*public List getZjTableList(String date, String fybm){  
    	String sql = "SELECT jbfy,fyjc as fy," + 
        			"sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' then result else 0 end )as lastyearzs," + 
        			"sum(case when date='"+date+"' then result else 0 end) as yearzs," + 
        			"(sum(case when date='"+date+"' then result else 0 end) - sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' then result else 0 end )) as cz from sfzs_result_csx,sfzs_czrk where sfzs_result_csx.jbfy=sfzs_czrk.fybm and sfzs_czrk.year='"+NumUtil.getPastDate(date,"yyyy")+"' and kind ='中' and jbfy like'"+fybm.substring(0, 1)+"%' GROUP BY jbfy,fyjc ORDER BY yearzs";
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }*/
    //直辖市中院排名表格
    /*public List getZjZxTableList(String date, String fybm){  
    	StringBuffer sql = new StringBuffer( "SELECT\r\n" + 
    			"	jbfy,\r\n" + 
    			"	fyjc AS fy,\r\n" + 
    			"	sum( CASE WHEN date = '"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' THEN result ELSE 0 END ) AS lastyearzs,\r\n" + 
    			"	sum( CASE WHEN date = '"+date+"' THEN result ELSE 0 END ) AS yearzs,\r\n" + 
    			"	(\r\n" + 
    			"	sum( CASE WHEN date = '"+date+"' THEN result ELSE 0 END ) - sum( CASE WHEN date = '"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' THEN result ELSE 0 END ) \r\n" + 
    			"	) AS cz \r\n" + 
    			"FROM\r\n" + 
    			"	sfzs_result_csx,\r\n" + 
    			"	sfzs_czrk \r\n" + 
    			"WHERE\r\n" + 
    			"	sfzs_result_csx.jbfy = sfzs_czrk.fybm \r\n" + 
    			"	AND sfzs_czrk.YEAR = '"+NumUtil.getPastDate(date,"yyyy")+"' \r\n" + 
    			"	AND kind = '辖' \r\n" + 
    			"	AND jbfy LIKE '"+fybm.substring(0, 1)+"%' AND right(jbfy,1)='0' AND jbfy <>'"+fybm.substring(0, 1)+"00'\r\n" + 
    			"GROUP BY\r\n" + 
    			"	jbfy,\r\n" + 
    			"	fyjc \r\n" + 
    			"ORDER BY\r\n" + 
    			"	yearzs");
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //辖区排名表格
    public List getXqTableList(String date, String fybm){  
    	String sql = "SELECT jbfy,replace(fymc,'人民法院','法院') as fy,\r\n" + 
    			"sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' then result else 0 end )as lastyearzs,\r\n" + 
    			"sum(case when date='"+date+"' then result else 0 end) as yearzs,\r\n" + 
    			"(sum(case when date='"+date+"' then result else 0 end) - sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' then result else 0 end )) as cz from sfzs_result_csx where kind ='辖' and jbfy like'"+fybm.substring(0, 2)+"%' and jbfy<>'"+fybm.substring(0, 2)+"'||'0' and right(fymc,5)<>'（已撤销）' GROUP BY jbfy,fymc ORDER BY yearzs";
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }
    //区县法院在所属高院辖区内排名
    public List getRankInGyxq(String date, String fybm){  
    	String sql = "SELECT\r\n" + 
    			"	rank \r\n" + 
    			"FROM\r\n" + 
    			"	(\r\n" + 
    			"SELECT\r\n" + 
    			"	jbfy,\r\n" + 
    			"	fy,\r\n" + 
    			"	lastyearzs,\r\n" + 
    			"	yearzs,\r\n" + 
    			"	cz,\r\n" + 
    			"	rank ( ) over ( ORDER BY yearzs DESC ) AS rank \r\n" + 
    			"FROM\r\n" + 
    			"	(SELECT jbfy,replace(fymc,'人民法院','法院') as fy,\r\n" + 
    			"sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' then result else 0 end )as lastyearzs,\r\n" + 
    			"sum(case when date='"+date+"' then result else 0 end) as yearzs,\r\n" + 
    			"(sum(case when date='"+date+"' then result else 0 end) - sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' then result else 0 end )) as cz from sfzs_result_csx where kind ='辖' and jbfy like'"+fybm.substring(0, 1)+"%' and RIGHT ( jbfy, 1 ) <> '0' and right(fymc,5)<>'（已撤销）' GROUP BY jbfy,fymc ORDER BY yearzs) t \r\n" + 
    					"	) t1 \r\n" + 
    					"WHERE\r\n" + 
    					"	jbfy ='"+fybm+"'";
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }*/
    //同等规模城市排名表格
    /*public List getTdZjTableList(String date, String fybm){  
    	String sql = "SELECT jbfy,replace(fyjc,'法院','')||'法院' AS fy,\r\n" + 
    			"sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' then result else 0 end )as lastyearzs,\r\n" + 
    			"sum(case when date='"+date+"' then result else 0 end) as yearzs,\r\n" + 
    			"(sum(case when date='"+date+"' then result else 0 end) - sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' then result else 0 end )) as cz from sfzs_result_csx,sfzs_czrk where sfzs_result_csx.jbfy=sfzs_czrk.fybm and sfzs_czrk.year='"+NumUtil.getPastDate(date,"yyyy")+"' and (kind = '中' or (kind='高' and fybm in('100','200','900','M00'))) and size in (select size from sfzs_czrk where sfzs_czrk.year='"+NumUtil.getPastDate(date,"yyyy")+"' and fybm = '"+fybm+"') GROUP BY jbfy,fyjc ORDER BY yearzs";
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }*/
    //同等直辖市中院排名表格
    /*public List getTdZxTableList(String date, String fybm){  
    	String sql = "SELECT jbfy,fyjc as fy,\r\n" + 
    			"sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' then result else 0 end )as lastyearzs,\r\n" + 
    			"sum(case when date='"+date+"' then result else 0 end) as yearzs,\r\n" + 
    			"(sum(case when date='"+date+"' then result else 0 end) - sum(case when date='"+NumUtil.getPastDate(date,"yyyy-MM-dd")+"' then result else 0 end )) as cz from sfzs_result_csx,sfzs_czrk where sfzs_result_csx.jbfy=sfzs_czrk.fybm and sfzs_czrk.year='"+NumUtil.getPastDate(date,"yyyy")+"' and kind ='辖' AND (jbfy like'1%0' or jbfy like'2%0' or jbfy like'9%0' or jbfy like'M%0' )\r\n" + 
    					"	AND jbfy not in('100','200','900','M00') GROUP BY jbfy,fyjc ORDER BY yearzs";
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }*/
    
    //同等收案量排名表格
   /* public List getTdSalTableList(String date, String fybm){  
    	StringBuffer sql = new StringBuffer("SELECT\r\n" + 
    			"	sal,jbfy,\r\n" + 
    			"	REPLACE ( fyjc, '法院', '' ) || '法院' AS fy,\r\n" + 
    			"	result\r\n" + 
    			"FROM\r\n" + 
    			"sfzs_result_csx,sfzs_czrk \r\n" + 
    			"WHERE\r\n" + 
    			"sfzs_result_csx.jbfy = sfzs_czrk.fybm \r\n" + 
    			"	AND sfzs_czrk.YEAR = '"+NumUtil.getPastDate(date,"yyyy")+"' \r\n" + 
    			"	AND sfzs_result_csx.date = '"+date+"'\r\n" + 
    			"	AND kind = '中' \r\n" + 
    			"	AND sal IN ( SELECT sal FROM sfzs_czrk WHERE sfzs_czrk.YEAR = '"+NumUtil.getPastDate(date,"yyyy")+"' AND fybm = '"+fybm+"' ) \r\n ORDER BY result");
    	Query query = entityManager.createNativeQuery(sql.toString());  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }*/
    
    //区县法院基本信息
    /*public List getQxInfo(String date, String fybm){  
    	String sql = "select (select count(*) from sfzs_czrk where sfzs_czrk.year='"+NumUtil.getPastDate(date,"yyyy")+"' and size in (select size from sfzs_czrk where sfzs_czrk.year='"+NumUtil.getPastDate(date,"yyyy")+"' and fybm = '"+fybm+"')) as c,(select fyjc from sfzs_czrk where sfzs_czrk.year='"+NumUtil.getPastDate(date,"yyyy")+"' and fybm = '"+fybm.substring(0, 1)+"'||'00') as xq,num,jbfy,fy,rank,size from(select num,jbfy,fy,result,size,rank() over(order by result) as rank from(select size,sfzs_czrk.num as num,jbfy,fyjc as fy,result from sfzs_result_csx,sfzs_czrk where sfzs_result_csx.jbfy=sfzs_czrk.fybm and sfzs_czrk.year='"+NumUtil.getPastDate(date,"yyyy")+"' and kind ='中' and date='"+date+"' and size in (select size from sfzs_czrk where sfzs_czrk.year='"+NumUtil.getPastDate(date,"yyyy")+"' and fybm = '"+fybm+"') order by result) t )t1 where jbfy='"+fybm+"'";
    	Query query = entityManager.createNativeQuery(sql);  
        query.unwrap(SQLQuery.class).setResultTransformer(Transformers.ALIAS_TO_ENTITY_MAP);  
        List rows = query.getResultList();  
        return rows;
    }*/
    public void getCsxzsGy(Map dataMap,String kind,String csxdate,String fybm ,String fymc) {
    	getSectionOne(dataMap,kind,csxdate,fybm);
		dataMap.put("sfgl_csxzs", "--");
		dataMap.put("sfgl_csx_tb", "--");//同比
		List qgTableList = baseService.getQgTableList("sfzs_result_csx", "year", csxdate, false, false, "");
		List qgSelfList = baseService.getQgTableList("sfzs_result_csx", "year", csxdate, true, false, "");
		List qgTableList3 = new ArrayList();
		Map rowSelf = new HashMap();
		for(int i=qgTableList.size()-1;i>=0;i--) {//为全国报告增加
			Map row = (Map)qgTableList.get(i);
			String fy = row.get("fy").toString();
			if(fy.contains("北京")||fy.contains("天津")||fy.contains("上海")||fy.contains("重庆")) {//取第一个不是直辖市的高院
				continue;
			}else {
				dataMap.put("sfgl_csx_rank1_qg",NumUtil.toSheng(fy)+"法院");
				if(fymc.equals("全国")) {
					List zjList = baseService.getZjTableList("sfzs_result_csx", "year", csxdate, row.get("jbfy").toString(), false, "");
					Map rowzj = (Map)zjList.get(0);
					String rankZJ1 = rowzj.get("fy").toString();
					dataMap.put(37,NumUtil.toSheng(fy));
					dataMap.put("sfgl_csx_rank1_zj", rankZJ1);//中级排名第一
				}
				break;
			}
		}
		baseService.getGyDataIntoMap(dataMap, qgTableList, qgSelfList, qgTableList3, fymc, "sfgl_csx");
		List zjTableList = baseService.getZjTableList("sfzs_result_csx", "year", csxdate, fybm, false, "");
		//List gylist = getGsList("辖",csxdate,fybm);//获取公开指数概况
		List gylist = baseService.getSelfZs("sfzs_result_csx", "year", csxdate, fybm, "");
		baseService.getZyDetailDataIntoMap(dataMap, zjTableList, gylist, fymc, "sfgl_csx");
		//处理表格
		if(fymc.equals("全国")) {
			dataMap.put("array18",qgTableList);
		}else {
			dataMap.put("array35",qgTableList3);
			dataMap.put("array36",zjTableList);
		}
		
	}
    public void getCsxzsZy(Map dataMap, String kind, String csxdate, String fybm, String fymc) {//裁判文书公开指数    	   	
		List zjTableList = new ArrayList();
		List tdTableList = new ArrayList();
		List xqTableList = baseService.getXqTableList("sfzs_result_csx", "year", csxdate, fybm, false,"");
		List zylist = baseService.getSelfZs("sfzs_result_csx", "year", csxdate, fybm, "");
		baseService.getQxDetailDataIntoMap(dataMap, xqTableList, zylist, fymc, "sfgl_csx");
		
		List tsTableList = baseService.getTsTableList("sfzs_result_csx", "year", csxdate, fybm, false,"");
    	baseService.getTsDataIntoMap(dataMap, tsTableList, fybm, "sfgl_csx");
		if(fybm.startsWith("1")||fybm.startsWith("2")||fybm.startsWith("M")||fybm.startsWith("9")) {
			getSectionOne(dataMap,"辖",csxdate,fybm);
			//同等直辖市
			tdTableList = baseService.getTdZxTableList("sfzs_result_csx", "year", csxdate, fybm, false,"");
			baseService.getTdZxDataIntoMap(dataMap, tdTableList, fybm, "sfgl_csx");
			zjTableList = baseService.getZjZxTableList("sfzs_result_csx", "year", csxdate, fybm, false,"");
			baseService.getZyDataIntoMap(dataMap, zjTableList, fybm, "sfgl_csx");
			//处理表格
			dataMap.put("array59",zjTableList);
			dataMap.put("array60",tdTableList);
			dataMap.put("array61",xqTableList);
			dataMap.put("array62",tsTableList);
		}else {
			getSectionOne(dataMap,kind,csxdate,fybm);
			//同等规模城市
			List infoList = baseService.getQxInfo("sfzs_result_csx", "year", csxdate, fybm, false, "");
			tdTableList = baseService.getTdZjTableList("sfzs_result_csx", "year", csxdate, fybm, false, "");
			baseService.getTdCityDataIntoMap(dataMap, infoList, tdTableList, "sfgl_csx",fybm);
			zjTableList = baseService.getZjTableList("sfzs_result_csx", "year", csxdate, fybm, false, "");
			baseService.getZyDataIntoMap(dataMap, zjTableList, fybm, "sfgl_csx");
			//同等收案量排行
			List tdSalTableList = baseService.getTdSalTableList("sfzs_result_csx", "year", csxdate, fybm, false, "");
			baseService.getTdSalDataIntoMap(dataMap, tdSalTableList, fymc,"sfgl_csx");	
			//处理表格
			dataMap.put("array63",zjTableList);
			dataMap.put("array64",tdTableList);
			dataMap.put("array65",xqTableList);
			dataMap.put("array66",tsTableList);
		}
	}
    
    public void getQxZs(Map dataMap,String kind, String csxdate, String fybm) {//区县法院指数
    	getSectionOne(dataMap,kind,csxdate,fybm);
    	List xqTableList = baseService.getXqTableList("sfzs_result_csx", "year", csxdate, fybm, false,"");
    	List gylist = baseService.getGyOrZyXqZs("sfzs_result_csx", "year", csxdate, fybm, "","高");
    	List zylist = baseService.getGyOrZyXqZs("sfzs_result_csx", "year", csxdate, fybm, "","中");
    	baseService.getQxDataIntoMap(dataMap, xqTableList, fybm, "sfgl_csx",gylist,zylist);
    	String ssxq_gy_rank = baseService.getRankInGyxq("sfzs_result_csx", "year", csxdate, fybm, false,"").get(0).toString();
    	dataMap.put("sfgl_csx_rank_gy",  "第" + NumUtil.toChineseNum(ssxq_gy_rank) + "位");
    	List tsTableList = baseService.getTsTableList("sfzs_result_csx", "year", csxdate, fybm, false,"");
    	baseService.getTsDataIntoMap(dataMap, tsTableList, fybm, "sfgl_csx");
    	dataMap.put("array29",xqTableList);
    	dataMap.put("array30",tsTableList);
    } 
    
    public void getSectionOne(Map dataMap,String kind,String csxdate,String fybm) {
    	List gslist = getGsList(kind,csxdate,fybm);//获取公开指数概况
    	Map mapYear = new HashMap();
    	int sj = 0;
    	double cx0006 = 0;
    	double cx0612 = 0;
    	double cx1224 = 0;
    	double cx2436 = 0;
    	double cx3600 = 0;
    	if(gslist.size()>0) {
    		mapYear = (Map)gslist.get(0);
    		sj = Integer.parseInt(mapYear.get("num").toString());
    		cx0006 = NumUtil.toNxiaoshu(mapYear.get("cx0006").toString(), 2);
    		cx0612 = NumUtil.toNxiaoshu(mapYear.get("cx0612").toString(), 2);
    		cx1224 = NumUtil.toNxiaoshu(mapYear.get("cx1224").toString(), 2);
    		cx2436 = NumUtil.toNxiaoshu(mapYear.get("cx2436").toString(), 2);
    		cx3600 = NumUtil.toNxiaoshu(mapYear.get("cx3600").toString(), 2);
    	}
    	dataMap.put("sfgl_csx_num", NumUtil.toWan(sj));//审结案件
		dataMap.put("sfgl_csx_0006", cx0006);
		dataMap.put("sfgl_csx_0612", cx0612);
		dataMap.put("sfgl_csx_1224", cx1224);
		dataMap.put("sfgl_csx_2436", cx2436);
		dataMap.put("sfgl_csx_3600", cx3600);
    }
   
}
